package dao;

import db.JdbcUtil;
import javabean.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    // 向数据库中添加用户记录的方法add(User user)，将对象user插入数据表中
    public void add(User user) throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        conn = JdbcUtil.getConnection();
        String sql = "insert into user_b(username,sex,userpassword,itemid)  values (?,?,?,?) ";
        ps = conn.prepareStatement(sql);
        ps.setString(1, user.getUserName());
        ps.setString(3, user.getUserPwd());
        ps.setString(2,user.getUserSex());
        ps.setString(4,user.getItemid());
        ps.executeUpdate();
        JdbcUtil.free(null, ps, conn);
    }
    //查询全部用户的方法QueryAll()
    public List<User> QueryAll() throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<User>();
        conn = JdbcUtil.getConnection();
        String sql = "select * from user_b";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            String xm= rs.getString("username");
            String xb= rs.getString("sex");
            String mm= rs.getString("userpassword");
            String tp= rs.getString("itemid");
            User user = new User(xm,xb,mm,tp);
            userList.add(user);
        }
        JdbcUtil.free(rs, ps, conn);
        return userList;
    }
}
